﻿Public Class QuanLyTienThuThang

    Private row_index As Integer = -1
    Private mapt As String = ""
    Private dt As New DataTable

    Private Sub XoaTrang()
        mapt = ""
        dateNgayThu.Value = DateTime.Now
        cbPhanLoai.Text = String.Empty
        cbTaiKhoan.Text = String.Empty
        cbNguoiThu.Text = String.Empty
        txtSoTienThu.Value = 0
        txtChiTiet.Text = String.Empty
    End Sub

    Private Sub GanText(ByVal index As Integer)
        Try
            dateNgayThu.Value = DataGridView1.Rows(index).Cells(0).Value
            cbPhanLoai.Text = DataGridView1.Rows(index).Cells(4).Value
            cbTaiKhoan.Text = DataGridView1.Rows(index).Cells(3).Value
            cbNguoiThu.Text = DataGridView1.Rows(index).Cells(2).Value
            txtSoTienThu.Text = DataGridView1.Rows(index).Cells(1).Value
            txtChiTiet.Text = DataGridView1.Rows(index).Cells(5).Value
            mapt = DataGridView1.Rows(index).Cells(6).Value
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        
    End Sub

    Private Sub QuanLiTienThuThang_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        cbThang.SelectedIndex = 0

        cbPhanLoai.DataSource = ProcFunc.ExecuteFuncSQL("select * from ThuNhap")
        cbPhanLoai.ValueMember = "MaThuNhap"
        cbPhanLoai.DisplayMember = "TenThuNhap"
        cbPhanLoai.SelectedIndex = -1

        'cbTaiKhoan.DataSource = ProcFunc.ExecuteFuncSQL("select distinct MaNganHang, TenNganHang from NganHang")
        'cbTaiKhoan.ValueMember = "MaNganHang"
        'cbTaiKhoan.DisplayMember = "TenNganHang"
        'cbTaiKhoan.SelectedIndex = -1

        cbNguoiThu.DataSource = ProcFunc.ExecuteFuncSQL("select * from [User]")
        cbNguoiThu.ValueMember = "MaUser"
        cbNguoiThu.DisplayMember = "TenUser"
        'cbNguoiThu.SelectedIndex = -1

        Dim str As String = "select pt.Ngay, pt.SoTien, us.TenUser, ng.TenNganHang, tn.TenThuNhap, pt.ChiTiet" &
                            ", pt.MaPhieuThu, us.MaUser, ng.MaNganHang, tn.MaThuNhap" &
                            " from PhieuThu pt, [User] us, NganHang ng, ThuNhap tn" &
                            " where pt.MaUser = us.MaUser and pt.MaNganHang = ng.MaNganHang and pt.MaThuNhap = tn.MaThuNhap" &
                            " order by pt.Ngay"
        Try
            dt = ProcFunc.ExecuteFuncSQL(str)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        If dt.Rows.Count > 0 Then
            DataGridView1.DataSource = dt
            'GanText(0)
            row_index = 0
        End If

    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        If e.RowIndex < 0 Or e.ColumnIndex < 0 Then
            Return
        End If
        row_index = e.RowIndex
        GanText(e.RowIndex)
    End Sub


    Private Sub btnSoanLai_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoanLai.Click
        XoaTrang()
    End Sub

    Private Sub btnThem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThem.Click

        If cbPhanLoai.Text <> "" And cbTaiKhoan.Text <> "" And cbNguoiThu.Text <> "" And txtSoTienThu.Text <> "" Then
            'Tao ma phieu thu
            Dim str = "00"
            Dim day As String = str.Substring(0, 2 - dateNgayThu.Value.Day.ToString.Length) & dateNgayThu.Value.Day
            Dim month As String = str.Substring(0, 2 - dateNgayThu.Value.Month.ToString.Length) & dateNgayThu.Value.Month
            Dim year As String = dateNgayThu.Value.Year.ToString.Substring(dateNgayThu.Value.Year.ToString.Length - 2, 2)
            mapt = ProcFunc.ExecuteFuncSSQL("select max(MaPhieuThu) from PhieuThu where MaPhieuThu like N'PT" & day & month & year & "%'").ToString()
            If mapt = String.Empty Then
                mapt = "PT" + day & month & year & "0001"
            Else
                mapt = "PT" & (mapt.Substring(2, mapt.Length - 2) + 1)
            End If
            '//

            'luu xuong co du lieu
            str = "insert into PhieuThu values ('" & mapt & "','" & cbNguoiThu.SelectedValue & "','" & cbTaiKhoan.SelectedValue & "','" & cbPhanLoai.SelectedValue & "','" &
                String.Format("{0:yyyy-MM-dd}", dateNgayThu.Value) & "','" & txtSoTienThu.Value.ToString.Trim & "',N'" & txtChiTiet.Text.Trim & "')"
            ProcFunc.ExecuteProcSQL(str)

            'Thêm mot dong vao datagridview khong can load lai data
            Dim row As DataRow
            row = dt.NewRow
            row(0) = dateNgayThu.Value.Date
            row(1) = txtSoTienThu.Value.ToString.Trim
            row(2) = cbNguoiThu.Text
            row(3) = cbTaiKhoan.Text
            row(4) = cbPhanLoai.Text
            row(5) = txtChiTiet.Text.Trim
            row(6) = mapt
            row(7) = cbNguoiThu.SelectedValue
            row(8) = cbTaiKhoan.SelectedValue
            row(9) = cbPhanLoai.SelectedValue
            dt.Rows.Add(row)

            'Trotoi dong vua them
            DataGridView1.CurrentCell = DataGridView1.Rows(dt.Rows.Count - 1).Cells(0)
            row_index = dt.Rows.Count - 1

            MessageBox.Show("Dữ liệu đã được thêm")

        Else
            MessageBox.Show("Xin vui lòng điền đủ thông tin")
        End If

    End Sub


    Private Sub btnXoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoa.Click
        If mapt = "" Then
            Return
        End If

        Dim res As MsgBoxResult = MsgBox("Ban co muon xoa", MsgBoxStyle.YesNo, "Thong bao")
        Select Case res
            Case MsgBoxResult.Yes

                'delete tren csdl
                Dim str = "delete from PhieuThu where MaPhieuThu = N'" & mapt & "'"
                ProcFunc.ExecuteProcSQL(str)

                XoaTrang()
                'Xoa tren d
                DataGridView1.Rows.RemoveAt(row_index)

                MessageBox.Show("Dữ liệu đã được xóa")

            Case MsgBoxResult.No
                Return
        End Select
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        If cbPhanLoai.Text <> "" And cbTaiKhoan.Text <> "" And cbNguoiThu.Text <> "" And txtSoTienThu.Text <> "" Then
            'Sua tren csdl
            Dim str As String = "update PhieuThu set MaUser = '" & cbNguoiThu.SelectedValue & "', MaNganHang = '" & cbTaiKhoan.SelectedValue & "', MaThuNhap = '" & cbPhanLoai.SelectedValue & "', Ngay = '" &
                String.Format("{0:yyyy-MM-dd}", dateNgayThu.Value) & "', SoTien ='" & txtSoTienThu.Value.ToString.Trim & "', ChiTiet = N'" & txtChiTiet.Text.Trim &
                "' where MaPhieuThu = '" & mapt & "'"
            ProcFunc.ExecuteProcSQL(str)

            'Sua mot dong trong datagridview khong can load lai data
            Dim row As DataRow
            row = dt.Rows(row_index)
            row(0) = dateNgayThu.Value.Date
            row(1) = txtSoTienThu.Value.ToString.Trim
            row(2) = cbNguoiThu.Text
            row(3) = cbTaiKhoan.Text
            row(4) = cbPhanLoai.Text
            row(5) = txtChiTiet.Text.Trim
            row(6) = mapt
            row(7) = cbNguoiThu.SelectedValue
            row(8) = cbTaiKhoan.SelectedValue
            row(9) = cbPhanLoai.SelectedValue

            MessageBox.Show("Dữ liệu đã được sửa")

        Else
            MessageBox.Show("Xin vui lòng điền đủ thông tin")
        End If
    End Sub

    Private Sub cbThang_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbThang.SelectedIndexChanged
        Dim str As String = ""

        If cbThang.SelectedIndex = 0 Then
            str = "select pt.Ngay, pt.SoTien, us.TenUser, ng.TenNganHang, tn.TenThuNhap, pt.ChiTiet" &
                            ", pt.MaPhieuThu, us.MaUser, ng.MaNganHang, tn.MaThuNhap" &
                            " from PhieuThu pt, [User] us, NganHang ng, ThuNhap tn" &
                            " where pt.MaUser = us.MaUser and pt.MaNganHang = ng.MaNganHang and pt.MaThuNhap = tn.MaThuNhap" &
                            " order by pt.Ngay"
        Else
            str = "select pt.Ngay, pt.SoTien, us.TenUser, ng.TenNganHang, tn.TenThuNhap, pt.ChiTiet" &
                            ", pt.MaPhieuThu, us.MaUser, ng.MaNganHang, tn.MaThuNhap" &
                            " from PhieuThu pt, [User] us, NganHang ng, ThuNhap tn" &
                            " where pt.MaUser = us.MaUser and pt.MaNganHang = ng.MaNganHang and pt.MaThuNhap = tn.MaThuNhap " &
                            " and Month(Ngay) = '" & cbThang.SelectedIndex.ToString + "'" &
                            " order by pt.Ngay"
        End If

        Try
            dt = ProcFunc.ExecuteFuncSQL(str)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        DataGridView1.DataSource = dt
        If dt.Rows.Count > 0 Then
            GanText(0)
            row_index = 0
        End If
    End Sub

    Private Sub cbNguoiThu_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbNguoiThu.SelectedIndexChanged
        cbTaiKhoan.DataSource = ProcFunc.ExecuteFuncSQL("select distinct MaNganHang, TenNganHang from NganHang where MaUser = '" & cbNguoiThu.SelectedValue.ToString & "'")
        cbTaiKhoan.ValueMember = "MaNganHang"
        cbTaiKhoan.DisplayMember = "TenNganHang"
        'cbTaiKhoan.SelectedIndex = -1
        GanText(row_index)
    End Sub
End Class